#!/usr/bin/env python
# coding:utf-8


import logging
from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class MMFIndexBrepo(DataTableTransfer):
    """
    基金债券回购融资情况(TQ_FD_FUNDBREPOFIN) --> 货币基金专项指标(fund_money_market_index)
    """
    QUERY_SOURCE = '''select  b.SECURITYID as sec_uni_code,a.BEGINDATE as begin_date,a.ENDDATE as end_date,a.REPORTTYPE as report_type
    from TQ_FD_FUNDBREPOFIN a  inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) UNION ALL
    select  b.SECURITYID as sec_uni_code,a.BEGINDATE as begin_date,a.ENDDATE as end_date,a.REPORTTYPE as report_type
    from TQ_FD_FUNDASSDEV a inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302)  UNION ALL 
    select b.SECURITYID as sec_uni_code,NULL  as begin_date,a.PUBLISHDATE as end_date,NULL as report_type
   from TQ_FD_CURAVGRMMONF a  inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) 
    and a.PUBLISHDATE>='20150101';'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.begin_date,'-','') as begin_date,
    replace(f.end_date,'-','') as end_date,report_type  from fund_money_market_index f
    join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',b.SECURITYID) as SECURITYID,a.BEGINDATE,a.ENDDATE,a.REPORTTYPE,a.REPEDBDREPOBALA,
    # a.BBACKFINANCING,a.REPINBDREBPROFD,a.REPINOUTREPROFD,a.REPEDBDREPROFD,a.REPEDOUTREPROFD from TQ_FD_FUNDBREPOFIN a
    # inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) limit %s, 1000;''')

    TABLE = 'fund_money_market_index'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False, 'report_type':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = MMFIndexBrepoMap
    MAP_VALUES = MMFIndexBrepoValueMap


class MMFIndexAssdev(DataTableTransfer):
    """
    基金资产净值偏离(TQ_FD_FUNDASSDEV) --> 货币基金专项指标(fund_money_market_index)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD',b.SECURITYID) as SECURITYID,a.BEGINDATE,a.ENDDATE,a.REPORTTYPE,a.AVGREPORDEVIONM,
    a.HIGHREPORDEVION,a.LOWREPORDEVION,a.AVGREPORDEVION from TQ_FD_FUNDASSDEV a 
    inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD',b.SECURITYID) as SECURITYID,a.BEGINDATE,a.ENDDATE,a.REPORTTYPE,
    a.AVGREPORDEVIONM,a.HIGHREPORDEVION,a.LOWREPORDEVION,a.AVGREPORDEVION from TQ_FD_FUNDASSDEV a 
    inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) 
    and a.ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = ('''select count(1) from TQ_FD_FUNDASSDEV a inner join TQ_OA_STCODE b on a.SECODE=b.SECODE 
    where b.SETYPE in (301,302);''')  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_FUNDASSDEV a inner join TQ_OA_STCODE b 
    on a.SECODE=b.SECODE where b.SETYPE in (301,302) and a.ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL

    TABLE = 'fund_money_market_index'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'begin_date', 'end_date', 'report_type')  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = MMFIndexAssdevMap
    MAP_VALUES = MMFIndexAssdevValueMap


class MMFIndexCurAvgMMONF(DataTableTransfer):
    """
    货币式基金平均剩余期限(TQ_FD_CURAVGRMMONF) --> 货币基金专项指标(fund_money_market_index)
    仅同步2015年(含2015年)以后的数据
    """
    QUERY_SOURCE = '''select  b.SECURITYID as sec_uni_code,a.PUBLISHDATE as end_date from TQ_FD_CURAVGRMMONF a 
    inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302) 
    and a.PUBLISHDATE>='20150101' ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date
    from fund_money_market_index f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',b.SECURITYID) as SECURITYID,a.PUBLISHDATE,a.AVGRESMAT,
    # a.AVGMAXRESMAT,a.AVGMINRESMAT from TQ_FD_CURAVGRMMONF a
    # inner join TQ_OA_STCODE b on a.SECODE=b.SECODE where b.SETYPE in (301,302)
    # and a.PUBLISHDATE>='20150101' limit %s, 1000;''')

    TABLE = 'fund_money_market_index'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
    }

    MAP_FIELDS = MMFIndexCurAvgMMONFMap
    MAP_VALUES = MMFIndexCurAvgMMONFValueMap
    IDX = ('sec_uni_code', )

    @classmethod
    def upset_mysql(cls, table, rows):
        """
        导入数据至目标库(中心库)
        仅做数据更新处理
        :param table:
        :param rows:
        :return:
        """

        for index, row in enumerate(rows):
            source_value = cls.find_record(table, row, cls.SOURCE_ID) if cls.SOURCE_ID else None
            if source_value:
                cls.upset(table=table, row=row, column_value=source_value, upset=True)

    @classmethod
    def find_record(cls, table, row, columns=tuple()):
        """
        查找记录
        :param table:
        :param row:
        :param columns:
        :return:
        """

        conditions = ''
        values = list()
        for index, column in enumerate(columns):
            if column in cls.MAP_VALUES.keys():
                map_value = cls.MAP_VALUES[column]
                if map_value.keys()[0] == 'SQL':  # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    value = cls.exec_sql(conf, query, (row[cls.MAP_FIELDS[column]], ), default_value)
                else:
                    value = map_value.get(row[cls.MAP_FIELDS[column]], None)
            else:     # 直接取值
                value = row[cls.MAP_FIELDS[column]]

            # 查询走索引，提高检索速度
            if column in cls.IDX:
                conditions += ''' and {0}=%s'''.format(column)
            else:
                conditions += ''' and ifnull({0},'')=%s'''.format(column)

            # 排空和‘19000101’(oracle默认值)
            if value is None or value == '19000101':
                value = ''
            # 修正日期时间类型
            if column in cls.DATETIME.keys() and value:
                value = '{0}-{1}-{2}'.format(value[:4], value[4:6], value[6:])
            values.append(value)

        conn = MysqlUtils.connect(cls.data_center)
        query_sql = ('''select {2} from {0} where{1} 
        and report_type in(1015037002001,1015037002002,1015037002003,1015037002004);'''
                     .format(table, conditions[4:], cls.COLUMN_ID))
        cursor = MysqlUtils.common_query(conn=conn, query=query_sql, args=tuple(values))
        if cursor and cursor.rowcount:
            row = cursor.fetchone()[0]
            cursor.close()
        else:
            row = None
        conn.close()
        return row


class MMFRMaturity(DataTableTransfer):
    """
    货币式基金平均剩余期限(TQ_FD_CURAVGRMMONF) --> 货币基金平均剩余期限分布(fund_money_market_remain_maturity)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,PUBLISHDATE as end_date from TQ_FD_CURAVGRMMONF  ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date
    from fund_money_market_remain_maturity f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) SECURITYID,DECLAREDATE,PUBLISHDATE,D30ASSETNAVRTO,D30ASSETFLTG397D,
    # D30LIABNAVRTO,D30LIABFLTG397D,D30T60DASSETNAVRTO,D30T60DASSETFLTG397D,D30T60DLIABNAVRTO,D30T60DLIABFLTG397D,
    # D60T90DASSETNAVRTO,D60T90DASSETFLTG397D,D60T90DLIABNAVRTO,D60T90DLIABFLTG397D,D90T120DASSETNAVRTO,
    # D90T120DASSETFLTG397D,D90T120DLIABNAVRTO,D90T120DLIABFLTG397D,D90T180DASSETNAVRTO,D90T180DASSETFLTG397D,
    # D90T180DLIABNAVRTO,D90T180DLIABFLTG397D,D120ASSETNAVRTO,D120ASSETFLTG397D,D120LIABNAVRTO,D120LIABFLTG397D,
    # D180ASSETNAVRTO,D180ASSETFLTG397D,D180LIABNAVRTO,D180LIABFLTG397D,ASSETACCNAV, LIABACCNAV
    # from TQ_FD_CURAVGRMMONF limit %s, 1000;''')

    TABLE = 'fund_money_market_remain_maturity'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = MMFRMaturityMap
    MAP_VALUES = MMFRMaturityValueMap


class FundStockTrading(DataTableTransfer):
    """
    基金股票交易金额表(TQ_FD_SKTRADING) --> 基金股票交易金额(fund_stock_trading)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,BEGINDATE as begin_date,ENDDATE as end_date from TQ_FD_SKTRADING ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.begin_date,'-','') as begin_date,
    replace(f.end_date,'-','') as end_date from fund_stock_trading f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code 
    where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,BUYSKTOTCOST,
    # SALESKTOTCOST,TOTFDNAVBEG,TOTFDNAVEND,SKMKTCAPBEG,SKMKTCAPEND,ASSETSACTIVE,TURNOVERRATIO,ISSTAT
    # from TQ_FD_SKTRADING limit %s, 1000;''')

    TABLE = 'fund_stock_trading'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundStockTradingMap
    MAP_VALUES = FundStockTradingValueMap


class FundTCSsion(DataTableTransfer):
    """
    分资产交易统计(TQ_FD_ASSETTRADESTAT) --> 基金租用交易席位及交易状况(fund_trading_commission)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEENDDATE as end_date ,BROKERNAME as broker_name from TQ_FD_ASSETTRADESTAT ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,broker_name
    from fund_trading_commission f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,TRADEBEGDATE,TRADEENDDATE,BROKERNAME,
    # concat('FD',BROKERID) as BROKERID,LENTSEATQUT,COMM,ACCTOTCOMM,STKTRADEQUT,ACCSTKTRADEQUT,
    # ifnull(NATDEBTTRADEQUT,0)+ifnull(ABSTRADEQUT,0) as BONDTRADINGAMOUNT,
    # ifnull(ACCNATDEBTTRADEQUT,0)+ifnull(ACCABSTRADEQUT,0) as TOTBONDTRADINGAMOUNTRATIO,REPUTRADEQUT,ACCREPUTRADEQUT,
    # WRTVOL,ACCWRTTRADEQUT,SECURITYVOL,ACCSECURITYTRADEQUT,ISSTAT,MEMO from TQ_FD_ASSETTRADESTAT limit %s, 1000;''')

    TABLE = 'fund_trading_commission'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'broker_name':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundTCSsionMap
    MAP_VALUES = FundTCSsionValueMap


if __name__ == '__main__':

    log_path = '../logs/export_fund_asset2.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # MMFIndexBrepo.work()
    # MMFIndexAssdev.work()
    # MMFIndexCurAvgMMONF.work()
    # FundStockTrading.work()
    FundTCSsion.work()
